<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

class HT_Model_administrator_models_news extends Zend_Db_Table {//ten class fai viet hoa

   protected $_db;

    public function __construct() {
        $this->_name = "news";
        $this->_db = Zend_Registry::get('dbMain');
        parent::init();
    }
    
    public function getCategory($categoryId){
    	$sql = "SELECT * FROM category WHERE id = ? LIMIT 1";
    	return $this->_db->fetchRow($sql,array($categoryId));
    }
    
    public function getCategoryNews($categoryId,$hotnewsLimit = 40, $topNewsLimit = 8){
    	$info = array();
    	$deniedIds = null;
    	$filter = array('categoryIds'=>array($categoryId));
    	$hotNews = $this->getListNews($hotnewsLimit,$filter);
    	$info['category']	= $this->getCategory($categoryId);
    	$info['hotNews'] 	= $hotNews;
    	 
    	foreach((array)$hotNews as $news){
    		$deniedIds[] = $news['news_id'];
    	}
    	 
    	$filter = array('categoryIds'=>array($categoryId),'orderBy'=>'news.view','direction'=>'DESC');
    	if(is_array($deniedIds) && sizeof($deniedIds) >0) $filter['deniedIds'] = $deniedIds;
    	$info['topNews'] = $this->getListNews($topNewsLimit,$filter);
    	return $info;
    }
    
    public function getGroupInfo($groupId){
    	$sql = "SELECT * FROM news_group WHERE group_id = ? LIMIT 1";
    	return $this->_db->fetchRow($sql,array($groupId));
    }
    
    public function getGroupNews($groupId,$hotnewsLimit = 8, $cateNewsLimit = 4,$topNewsLimit = 8){
    	$info = array();
    	$deniedIds = null;
    	$filter = array('groupId'=>array($groupId));
    	$hotNews = $this->getListNews($hotnewsLimit,$filter);
    	
    	$info['group'] = $this->getGroupInfo($groupId);
    	
    	$info['hotNews'] = $hotNews;
    	
    	foreach((array)$hotNews as $news){
    		$deniedIds[] = $news['news_id'];
    	}
    	
    	$filter = array('groupId'=>array($groupId),'orderBy'=>'news.view','direction'=>'DESC');
    	if(is_array($deniedIds) && sizeof($deniedIds) >0) $filter['deniedIds'] = $deniedIds;
    	$info['topNews'] = $this->getListNews($topNewsLimit,$filter);
    	
    	$categoryList = $this->getCategoryByGroup($groupId);
    	
    	for($i=0;$i<sizeof($categoryList);$i++){
    		$filter = array('categoryIds'=>array($categoryList[$i]['category_id']));
    		if(is_array($deniedIds) && sizeof($deniedIds) >0) $filter['deniedIds'] = $deniedIds;
    		$categoryList[$i]['newsList'] = $this->getListNews($cateNewsLimit,$filter);
    		
    	}
    	$info['categoryList'] = $categoryList;
    	return $info;
    }
    
    public function getDetailNews($newsId,$limit = 8){
    	$info 			= array();
    	$news 			= $this->getNews($newsId);
    	$categoryIds 	= array($news['category_id']);
    	$deniedIds		= array($newsId);
    	$filter 		= array('categoryIds'=>$categoryIds,'deniedIds'=>$deniedIds,'orderBy'=>'news.view','direction'=>'DESC');
    	$relatedNews 	= $this->getListNews(4,$filter);
    	foreach((array)$relatedNews as $item){
    		$deniedIds[] = $item['news_id'];
    	}
    	
    	$filter 				= array('deniedIds'=>$deniedIds,'orderBy'=>'news.view','direction'=>'DESC');
    	$topNews				= $this->getListNews($limit,$filter);
    	
    	$info['news'] 		 	= $news;
    	$info['relatedNews'] 	= $relatedNews;
    	$info['topNews'] 		= $topNews;
    	
    	return $info;
    }
    
    
    
    function getNews($newsId){
    	$sql = "SELECT 
    				news.id_news news_id, news.title_vn title, news.image , news.desc_vn description, news.content_vn content,
    				grp.group_id,cate.id category_id
		    	FROM news
		    	INNER JOIN link_news_category lnk ON news.id_news = lnk.id_news
		    	INNER JOIN category cate ON lnk.id_category = cate.id
		    	INNER JOIN news_group grp ON cate.group_id = grp.group_id
		    	WHERE news.status = 'active' AND news.id_news = ? LIMIT 1";
    	return $this->_db->fetchRow($sql,array($newsId));
    }
    
    public function getTrainingNews($trainingLimit = 8, $phpLimit = 12,$seoLimit = 12){
    	$info = array();
    	$deniedIds = null;
    	$filter 				= array('groupId'=>array(1));
    	$seoNews 				= $this->getListNews($seoLimit,$filter);
    	$filter 				= array('groupId'=>array(2));
    	$trainingNews 			= $this->getListNews($trainingLimit,$filter);
    	$filter 				= array('groupId'=>array(3));
    	$phpNews 				= $this->getListNews($phpLimit,$filter);
    	
    	$info['trainingNews'] 	= $trainingNews;
    	$info['phpNews'] 		= $phpNews;
    	$info['seoNews'] 		= $seoNews;
    	    	
    	return $info;
    }
    
    public function getHomeNews($seoLimit = 8, $keywordLimit = 12,$trainingLimit = 8){
    	$info = array();
    	$deniedIds = null;
    	$filter 				= array('groupId'=>array(1));
    	$seoNews 				= $this->getListNews($seoLimit,$filter);
    	$filter 				= array('groupId'=>array(2));
    	$trainingNews 			= $this->getListNews($trainingLimit,$filter);
    	$keywordList			= $this->getKeywordList($keywordLimit);
    	 
    	$info['seoNews'] 		= $seoNews;
    	$info['keywordList'] 	= $keywordList;
    	$info['trainingNews'] 	= $trainingNews;
    
    	return $info;
    }
    
    public function getKeywordList($limit = 12){
    	$sql = "SELECT sp.seopage_id,sp.tag_title, news.id_news news_id, news.title_vn title, news.image 
    			FROM seo_page sp
				INNER JOIN news ON news.id_news = sp.reference_news_id
    			WHERE news.image is not null
    			ORDER BY keyword_order DESC LIMIT ".(int)$limit;
    	return $this->_db->fetchAll($sql);
    }
    
    public function getGroupList(){
    	$sql = "SELECT group_id,group_name FROM news_group ORDER BY group_order";
    	return $this->_db->fetchAll($sql);
    }
    
    public function getCategoryByGroup($groupId){
    	$sql = "SELECT id category_id,category_name FROM category WHERE group_id = ? ORDER BY stt ASC ";
    	return $this->_db->fetchAll($sql,array($groupId));
    }
    
    public function getCategoryList(&$group){
    	$categoryIds  = array();
    	$groupId = $group['group_id'];
    	
    	$sql = "SELECT id category_id,category_name FROM category WHERE group_id = ? ORDER BY stt ASC ";
    	$categoryList = $this->_db->fetchAll($sql,array($groupId));
    	$group['categoryList'] = $categoryList;
    	foreach((array)$categoryList as $cate){
    		$categoryIds[] = $cate['category_id'];
    		$categoryNames[] = $cate['category_name'];
    	}
    	$group['categoryIds'] = $categoryIds;
    	$group['categoryNames'] = $categoryNames;
    }
    
    public function getListNews($limit = 4,$filter = array()){
    	$sqlPlus = $this->getListNews_sqlPlus($filter);    	
    	$sqlOrder = $this->getListNews_sqlOrder($filter);
    	$sql = "SELECT news.id_news news_id, news.title_vn title, news.image , news.desc_vn description
    			FROM news
    			INNER JOIN link_news_category lnk ON news.id_news = lnk.id_news
    			INNER JOIN category cate ON lnk.id_category = cate.id
    			INNER JOIN news_group grp ON cate.group_id = grp.group_id
    			WHERE news.status = 'active' $sqlPlus $sqlOrder LIMIT ".(int)$limit;
    	return $this->_db->fetchAll($sql);
    }
    
    private function getListNews_sqlOrder($filter){
    	if(isset($filter['orderBy']) && isset($filter['direction'])){
    		$orderBy 	= trim($filter['orderBy']);
    		$direction 	= trim($filter['direction']);
    		$sqlOrder = " ORDER BY $orderBy $direction ";
    	}else{
    		$sqlOrder = " ORDER BY news.created DESC ";
    	}
    	return $sqlOrder;
    }

    private function getListNews_sqlPlus($filter = array()){
    	$sqlPlus = null;
    	foreach((array)$filter as $key => $val){
    		$key = trim($key);
    		switch($key){
    			case 'categoryIds':
    				$sqlPlus .= " AND cate.id IN(".implode(',', $val).")";
    				break;
    			case 'deniedIds':
    				$sqlPlus .= " AND news.id_news NOT IN(".implode(',', $val).")";
    				break;
    			case 'groupId':
    				$sqlPlus .= " AND grp.group_id IN(".implode(',', $val).")";
    				break;
    		}
    	}
    	return $sqlPlus;
    }
    
    public function getNewsForSitemap(){
    	$sql = "SELECT id_news newsId, title_vn title 
    			FROM news 
    			WHERE status = 'active' AND date(created) = date('".date('Y-m-d')."')";
    	return $this->_db->fetchAll($sql);
    }
    
    public function getAll($where){
        $sql = "select * from news where ".$where;
        $retval = $this->_db->fetchAll($sql);
        
        return $retval;
    }
    public function findById($id){
        $sql = "select * from news where id_news = $id";
        $retval = $this->_db->fetchRow($sql);
        return $retval;
    }
    public function listall($where, $order){
        $query=$this->select();
        $query->from($this->_name,"*");
        $query->where($where);
        $query->order($order);
        return $query;            
    }    
    
    /* Vu added for tool tips*/
    
    public function formatTools($i,$noteText,$referenceInfo,$newsContent,$type="seo",$note = null){
    	switch($referenceInfo){
    		case 'can_nang_ba_bau':
    			$toolDesc 	= "Công cụ tính cân nặng bà bầu";
    			$toolLink	= WEB_PATH."/app/index/tool-weight";
    			break;
    		case 'so_lan_dap_cua_be':
    			$toolDesc 	= "Công cụ tính số lần đạp của bé";
    			$toolLink	= WEB_PATH."/app/index/tool-kick";
    			break;
    		case 'tinh_ngay_den_do':
    			$toolDesc = "Công cụ tính ngày đèn đỏ";
    			$toolLink	= WEB_PATH."/app/index/tool-red";
    			break;
    		case 'lich_tiem_chung':
    			$toolDesc = "Công cụ tạo lịch tiêm chủng cho bé";
    			$toolLink	= WEB_PATH."/app/index/tool-vaccine";
    			break;
    		case 'tao_the_cap_cuu':
    			$toolDesc = "Công cụ tạo thẻ cấp cứu";
    			$toolLink	= WEB_PATH."/app/index/tool-emergency";
    			break;
    		case 'tinh_ngay_rung_trung':
    			$toolDesc = "Công cụ tính ngày rụng trứng";
    			$toolLink	= WEB_PATH."/app/index/tool-ovulation";
    			break;
    	}
    
    	$tipContent  	= '<div class="note_box">';
    	$tipContent  	.= '<div class="note_box1 fl"><a  href="'.$toolLink.'"><div class="idea_icon fl"></div></a>'.$noteText.'<div class="cb"></div></div>';
    	$tipContent  	.= '<div class="note_box2 fl"><a  href="'.$toolLink.'"><div class="tool_'.$referenceInfo.' fl"></div></a><div class="tool_description fl">';
    	$tipContent  	.= '<div class="tool_description_line">'.$toolDesc.'</div>';
    	$tipContent  	.= '<div class="tool_description_line"><a  href="'.$toolLink.'">Nhấn vào đây để bắt đầu sử dụng</a></div>';
    	$tipContent  	.= '</div></div>';
    	$tipContent  	.= '<div class="cb"></div></div>';
    	if($type == 'seo'){
    		$newsContent 	= str_replace('__note'.$i.'__', $tipContent, $newsContent);
    	}else{
    		$note			= "note: $note end_note";
    		$newsContent 	= str_replace($note, $tipContent, $newsContent);
    	}
    
    	return $newsContent;
    }
    
    public function formatLink($i,$noteText,$referenceInfo,$newsContent,$type="seo",$note = null){
    	$tipContent  	= '<div class="note_box">';
    	$tipContent  	.= '<div class="note_box1 fl"><a  href="'.$referenceInfo.'"><div class="idea_icon fl"></div></a>'.$noteText.'<div class="cb"></div></div>';
    	$tipContent  	.= '<div class="note_box2 fl"><a  href="'.$referenceInfo.'"><div class="link_document fl"></div></a><div class="tool_description fl">';
    	$tipContent  	.= '<div class="tool_description_line">Để xem nội dung bài viết này, vui lòng <a  href="'.$referenceInfo.'">Nhấn vào đây</a></div>';
    	$tipContent  	.= '</div></div>';
    	$tipContent  	.= '<div class="cb"></div></div>';
    	if($type == 'seo'){
    		$newsContent 	= str_replace('__note'.$i.'__', $tipContent, $newsContent);
    	}else{
    		$note			= "note: $note end_note";
    		$newsContent 	= str_replace($note, $tipContent, $newsContent);
    	}
    
    	return $newsContent;
    }
    
    public function formatText($i,$seoText,$newsContent,$type='seo',$note=null){
    	$tipContent  	= '<div class="note_box"><div class="idea_icon fl"></div>'.$seoText.'<div class="cb"></div></div>';
    	if($type == 'seo'){
    		$newsContent 	= str_replace('__note'.$i.'__', $tipContent, $newsContent);
    	}else{
    		$note			= "note: $note end_note";
    		$newsContent 	= str_replace($note, $tipContent, $newsContent);
    	}
    	return $newsContent;
    }
    
    public function formatSEOContent($seoContent,$newsContent){
    	$arr 	= explode('note:', $seoContent);
    	for($i=1;$i<sizeof($arr);$i++){
    		$note 	= $arr[$i];
    		if(strpos($note,'reference_')){
    			list($noteText,$reference)		= explode('reference_',$note);
    			$noteText			= trim($noteText);
    			$reference 			= trim($reference);
    			$referenceType 		= substr($reference,0,4);
    			$referenceInfo 		= substr($reference,5);
    			$toolDesc 			= null;
    			$toolLink 			= null;
    			if($referenceType == 'tool'){
    				$newsContent = $this->formatTools($i,$noteText,$referenceInfo,$newsContent);
    			}elseif($referenceType == 'link'){
    				$newsContent	= $this->formatLink($i,$noteText,$referenceInfo,$newsContent);
    			}else{
    				$newsContent 	= $this->formatText($i,$note,$newsContent);
    			}
    		}else{
    			$newsContent 		= $this->formatText($i,$note,$newsContent);
    		}
    	}
    	return $newsContent;
    }
    
    public function formatNewsContent($newsContent){
    	$newsReturn = null;
    	$arr = explode('note:',$newsContent);
    	for($j=1; $j < sizeof($arr);$j++){
    		$item 		= $arr[$j];
    		$arr1 		= explode('end_note', $item);
    		$note 		= trim($arr1[0]);
    		if(strpos($note,'reference_')){
    			list($noteText,$reference)		= explode('reference_',$note);
    			$noteText			= trim($noteText);
    			$reference 			= trim($reference);
    			$referenceType 		= substr($reference,0,4);
    			$referenceInfo 		= substr($reference,5);
    			
    			$toolDesc 			= null;
    			$toolLink 			= null;
    			if($referenceType == 'tool'){
    				$newsContent 	= $this->formatTools($j,$noteText,$referenceInfo,$newsContent,'news',$note);
    			}elseif($referenceType == 'link'){
    				$newsContent	= $this->formatLink($j,$noteText,$referenceInfo,$newsContent,'news',$note);
    			}else{
    				$newsContent 	= $this->formatText($j,$note,$newsContent,'news',$note);
    			}
    		}else{
    			$newsContent 		= $this->formatText($j,$note,$newsContent,'news',$note);
    		}
    	}
    	$newsContent = str_replace('note:', '', $newsContent);
    	
    	for($i=1;$i<=50;$i++){
    		$newsContent = str_replace('__note'.$i.'__', '', $newsContent);
    	}
    	return $newsContent;
    }
    
    function newsContentAttach($text,$link){
    	$tipContent  	= '<div class="note_box">';
	    	$tipContent  	.= '<a  href="'.$link.'"><div class="link_document1 fl"></div></a>';
	    	$tipContent  	.= '<div class="tool_description1 fl">';
	    		$tipContent  	.= '<div class="tool_description_line">'.$text.'</div>';
	    	$tipContent  	.= '</div>';
    		$tipContent  	.= '<div class="cb"></div>';
    	$tipContent  	.= '</div>';    	
    	return $tipContent;
    }

    function getPartnerAttach($text,$link,$noteData,$noteLink = null){
    	$tipContent  	= '<div class="note_box">';
    	if($noteLink){
    		$tipContent  	.= '<div class="note_box1 fl"><a  href="'.$link.'"><div class="idea_icon fl"></div></a>'.$noteData.'<div class="cb"></div></div>';
    	}else{
    		$tipContent  	.= '<div class="note_box1 fl"><div class="idea_icon fl"></div>'.$noteData.'<div class="cb"></div></div>';
    	}
    	$tipContent  	.= '<div class="note_box2 fl"><a  href="'.$link.'"><div class="link_document fl"></div></a><div class="tool_description fl">';
    	$tipContent  	.= '<div class="tool_description_line">Để được tư vấn sức khỏe miễn phí từ các bác sĩ hàng đầu, vui lòng <a  href="'.$link.'">Nhấn vào đây</a></div>';
    	$tipContent  	.= '</div></div>';
    	$tipContent  	.= '<div class="cb"></div></div>';
    	return $tipContent;
    }
    
    /* End vu added*/
    
    public function insertCrawlerNews($data){
    	return $this->_db->insert('news',$data);
    }
	
	public function checkExistCrawlerID($crawler_id){
		$select = $this->_db->select()
							->from("news", array('crawler_id'))
							->where("crawler_id = '$crawler_id'");
		$result = $this->_db->fetchRow($select);
		if($result){
			return 1;
		}
		return 0;
	}
	public function getMaxIDNews(){
		$select = $this->_db->select()
							->from("news", array('max(id_news) as max'));
		$result = $this->_db->fetchRow($select);
		return $result['max'];
	}
	
	public function getServiceFromSer_phase($where="1=1"){
		$select = $this->_db->select()
							->from('ser_phase')
							->where($where);
		$result = $this->_db->fetchAll($select);
		return $result;
	}
	
	public function getNews_serphase($where="1=1"){
		$select = $this->_db->select()
							->from('news_serphase')
							->where($where);
		$result = $this->_db->fetchAll($select);
		return $result;									
	}
	
	public function delInNews_serphase($news_id) {
		return $this->_db->delete('news_serphase',"news_id = $news_id");
	}
	
	public function insertInNews_serphase($data) {
		return $this->_db->insert('news_serphase', $data);
	}
	
	//Action
	public function getActionsFromSerAction() {
		$select = $this->_db->select()
							->from('ser_action');
		$result = $this->_db->fetchAll($select);
		return $result;
	}
}

?>
